from DataAccess.DBConnFactory import DBConnFactory
from Routine.UpdateFX import *
from Routine.UpdateStockPrice import *

from string import Template
import timeit


def update_security_to_latest_price(ref_date):
	#DEBUG, Performance tuning
	#start = timeit.default_timer()
	
	update_fx_daily_price(ref_date)
	update_CH_stock_daily_price(ref_date)
	update_CH_fund_daily_price(ref_date)
	update_CH_bond_daily_price(ref_date)
	update_HK_stock_daily_price(ref_date)
	update_US_stock_daily_price(ref_date)
	update_futures_daily_settlement_price(ref_date)	
	
	#elapsed = (timeit.default_timer() - start)
	#print 'update_security_to_latest_price, ', elapsed

	
def calc_portf_mkt_val(ref_date, after_clearing=False):	
	#DEBUG, Performance tuning
	#start = timeit.default_timer()
	
	conn = DBConnFactory().get_db_connection('PKEDB')
	
	sql_tpl = Template('''select dp.ticker
				from daily_position_tick_ex dp
				left join comm_security_latest_price p
				on dp.ref_date=p.ref_date
				and dp.lticker=p.ticker
				where dp.ref_date=to_date('${DATE}','yyyy-mm-dd')
				and dp.security_type in ('STOCK','PNOTE','BOND','FUND','WARRANT','FUTURES')
				and nvl(p.close_price,-1)<0''')	
							
	sql_tpl1 = Template('''insert into portfolio_mkt_val 
				(ref_date,portfolio_id,mkt_val,currency,mkt_val_post)
				select TO_DATE('${DATE}','yyyy-mm-dd'),
					m2m.portfolio_id, 
					sum(round(m2m.mkt_val/fx.price,2)) mkt_val,
					pi.currency,
					sum(round(m2m.mkt_val/fx.price,2)) mkt_val_post
				from m2m_fast m2m
				join portfolio_info pi 
					on pi.portfolio_id=m2m.portfolio_id 
				join comm_fx_latest fx 
					on fx.ref_date=m2m.ref_date
					and fx.dom_curncy=m2m.local_curncy 
					and fx.for_curncy=pi.currency
				where m2m.ref_date=TO_DATE('${DATE}','yyyy-mm-dd') 
				group by m2m.portfolio_id,pi.currency''')
										
	# sql_tpl2 = Template('''update 
				# (select /*+BYPASS_UJVC*/ mv.mkt_val_post,mv.mkt_val,t.cash_in
				# from portfolio_mkt_val mv
				# join 
					# (select cs.ref_date,
						# pi.portfolio_id, 
						# sum(cs.amount/fx.price) cash_in
					# from portfolio_info pi
					# join cash_switch cs
					  # on pi.portfolio_id=cs.portfolio_id
					# join comm_fx_latest fx
					  # on cs.ref_date=fx.ref_date
					  # and fx.dom_curncy=cs.currency
					  # and fx.for_curncy=pi.currency
					# where cs.ref_date=TO_DATE('${DATE}','yyyy-mm-dd') 
					# group by pi.portfolio_id,cs.ref_date
					# )t
				# on mv.ref_date=t.ref_date
					# and mv.portfolio_id=t.portfolio_id)
				# set mkt_val_post=mkt_val+cash_in''')
				
	sql_tpl2 = Template('''merge into portfolio_mkt_val mv
				using (select cs.ref_date,
						pi.portfolio_id, 
						sum(cs.amount/fx.price) cash_in,
						pi.currency
					from portfolio_info pi
					join cash_switch cs
					  on pi.portfolio_id=cs.portfolio_id
					join comm_fx_latest fx
					  on cs.ref_date=fx.ref_date
					  and fx.dom_curncy=cs.currency
					  and fx.for_curncy=pi.currency
					where cs.ref_date=TO_DATE('${DATE}','yyyy-mm-dd') 
					group by pi.portfolio_id,cs.ref_date,pi.currency
					) t
				on (mv.ref_date=t.ref_date
					and mv.portfolio_id=t.portfolio_id)
				when matched then update set 
					mv.mkt_val_post=mv.mkt_val+t.cash_in
				when not matched then insert 
					values(t.ref_date,t.portfolio_id,0.0,t.currency,t.cash_in)''')
						
	if not after_clearing:
		cursor = conn.cursor()
		cursor.execute(sql_tpl.substitute(DATE=ref_date.isoformat()))
		r = cursor.fetchall()
		if r and len(r)>0:
			print "    error, Step 6: security price not available for mark to market,", r
			raise RuntimeError(), ("Security price not available")

		sql_text = sql_tpl1.substitute(DATE=ref_date.isoformat())
	else:
		sql_text = sql_tpl2.substitute(DATE=ref_date.isoformat())	
	conn.cursor().execute(sql_text)
	conn.commit()
	
	#elapsed = (timeit.default_timer() - start)
	#print 'calc_portf_mkt_val, ', elapsed
